/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.dhl.dao;

import com.dhl.shipment.Item;
import com.dhl.shipment.Shipment;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;

/**
 *
 * @author allan
 */
public class DHLDao {

    private static String CREATE_DHL_SHIPMENT = "{ call PKG_KALLSONYS.CREATE_DHL_SHIPMENT (?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?) }";
    private static String CREATE_DHL_SHIPMENT_ITEMS = "{ call PKG_KALLSONYS.CREATE_DHL_SHIPMENT_ITEMS (?, ?, ?, ?, ?, ?) }";
    private static String GET_DHL_SHIPMENT_STATUS = "{ call PKG_KALLSONYS.GET_DHL_SHIPMENT_STATUS (?, ?) }";
    private DataSource ds;

    public DHLDao(DataSource ds) {
        this.ds = ds;
    }

    /**
     * CreateShipment
     * @param shipment
     * @return boolean
     * @throws Exception 
     */
    public boolean CreateShipment(Shipment shipment) throws Exception {
        boolean retorno = false;
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(CREATE_DHL_SHIPMENT);

            stmt.setLong(1, Long.valueOf(shipment.getOrderId()));
            stmt.setString(2, shipment.getPartner());
            stmt.setString(3, shipment.getSupplier());
            stmt.setString(4, shipment.getAddresseeName());
            stmt.setString(5, shipment.getAddresseeLastName());
            stmt.setString(6, shipment.getCountry());
            stmt.setString(7, shipment.getCity());
            stmt.setString(8, shipment.getStreet());
            stmt.setString(9, shipment.getState());
            stmt.setString(10, shipment.getZipcode());
            stmt.setString(11, "6");
            //stmt.registerOutParameter(7, OracleTypes.INTEGER);
            stmt.execute();

            if (shipment.getItems() != null
                    && shipment.getItems().getItem() != null
                    && shipment.getItems().getItem().length > 0) {
                for (Item item : shipment.getItems().getItem()) {
                    stmt = con.prepareCall(CREATE_DHL_SHIPMENT_ITEMS);
                    stmt.setLong(1, Long.valueOf(item.getProdID()));
                    stmt.setString(2, item.getProductName());
                    stmt.setString(3, item.getPartNumber());
                    stmt.setLong(4, item.getPrice());
                    stmt.setInt(5, item.getQuantity());
                    stmt.setLong(6, Long.valueOf(shipment.getOrderId()));
                    stmt.execute();
                }
            }

            retorno = true;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }

    public String getShipmenStatus(long idOrder) throws Exception {
        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;
        String retorno = "-1";

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(GET_DHL_SHIPMENT_STATUS);
            stmt.setLong(1, idOrder);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;
                if (rs.next()) {
                    retorno = rs.getString("STATUS");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }
}
